package com.lovcreate.servletlearn;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;

/**
 * Created by Administrator on 2016/7/23.
 */
@WebServlet(name="DatabaseAccess",urlPatterns = {"/DatabaseAccess"})
public class DatabaseAccess extends HttpServlet {

    // JDBC 驱动器名称和数据库URL
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost/test";

    // 数据库的凭据
    static final String USER = "root";
    static final String PWD = "mysql";

    @Override
    public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // 设置响应内容类型
        response.setContentType("text/html; charset=utf-8");
        PrintWriter out = response.getWriter();
        String title = "数据库结果";
        String docType =
                "<!doctype html public \"-//w3c//dtd html 4.0 " +
                        "transitional//en\">\n";
        out.println(docType +
                "<html>\n" +
                "<head><title>" + title + "</title></head>\n" +
                "<body bgcolor=\"#f0f0f0\">\n" +
                "<h1 align=\"center\">" + title + "</h1>\n");

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            // 注册JDBC驱动器
            Class.forName(JDBC_DRIVER);
            // 打开一个连接
            conn = DriverManager.getConnection(DB_URL,USER,PWD);
            // 执行 SQL 查询
            stmt = conn.createStatement();
            String sql = "";
            sql = "select id, first, last ,age from employees";
            rs = stmt.executeQuery(sql);

            // 从结果集中提取数据
            while(rs.next()) {
                // 根据列名称检索
                int id = rs.getInt("id");
                int age = rs.getInt("age");
                String first = rs.getString("first");
                String last = rs.getString("last");

                // 显示值
                out.println("ID : " + id + "<br>");
                out.println(", Age : " + age + "<br>");
                out.println(", First : " + first + "<br>");
                out.println(", Last : " + last + "<br>");
            }
            out.println("</body></html>");

            // 清理环境
            rs.close();
            stmt.close();
            conn.close();
        } catch (SQLException se) {
            // 处理 JDBC 错误
            se.printStackTrace();
        } catch (Exception e) {
            // 处理Class.forName 错误
            e.printStackTrace();
        } finally {
            // 最后是用于关闭资源的块
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try{
                if(stmt!=null)
                    stmt.close();
            }catch(SQLException se2){
            }// 我们不能做什么
            try{
                if(conn!=null)
                    conn.close();
            }catch(SQLException se){
                se.printStackTrace();
            }//end finally try
        }

    }
}
